package cn.gzmli.dtt.common.service;

import cn.gzmli.dtt.common.utils.BaseService;
import cn.gzmli.dtt.common.dao.FaqDao;
import cn.gzmli.dtt.common.entity.Faq;
import cn.gzmli.dtt.common.entity.PageInfo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author Yzz
 */
@Service
@Transactional
public class FaqService extends BaseService<Faq> {

    @Autowired
    private FaqDao faqDao;

    /**
     * /获取列表
     * @param mQuestion
     * @param pageInfo
     * @param mpage
     * @return
     * @throws Exception
     */
    public List<Map<String, Object>> getAll2(String mQuestion, PageInfo pageInfo, int mpage) throws Exception {

        pageInfo.setCurPage(mpage - 1);
        /**
         * pageinfo
         */
        int a;

        StringBuilder sql = new StringBuilder();
        sql.append("SELECT * FROM faq ");
        if (mQuestion != null && mQuestion.length() > 0) {
            sql.append("WHERE question LIKE '%" + mQuestion + "%' ");
        }
        sql.append("ORDER BY count DESC ");
        sql.append("LIMIT " + pageInfo.getCurPage() * 12 + ",12");

        if (mQuestion != null && mQuestion.length() > 0) {
            StringBuilder sql2 = new StringBuilder();
            sql2.append("SELECT COUNT(1) FROM ( ");

            sql2.append("SELECT * FROM faq ");
            sql2.append("WHERE question LIKE '%" + mQuestion + "%' ");

            sql2.append(") t1");

            a = faqDao.getJdbcTemplate().queryForObject(sql2.toString(), Integer.class);
        } else {
            a = faqDao.getJdbcTemplate().queryForObject("SELECT COUNT(1) FROM faq", Integer.class);
        }

        pageInfo.setPageSize(12);
        pageInfo.setTotalRows(a);

        pageInfo.createTotalPages();
        return this.getDao().getJdbcTemplate().queryForList(sql.toString());
    }


    /**
     * 更新 点击次数
     */
    public void updateAddCount(int id) {
        StringBuilder sql = new StringBuilder();
        sql.append("UPDATE faq SET count = count +1 WHERE id =" + id);
        try {
            faqDao.getJdbcTemplate().execute(sql.toString());
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    /**
     * 添加新条目
     */
    public void addFaq(Faq faq) {

        StringBuilder sql = new StringBuilder();

        sql.append("INSERT INTO faq ( question, remarks, count, url, answer )");
        sql.append("VALUE");
        sql.append("( '"+faq.getQuestion()+"', '"+faq.getRemarks()+"'," +
                " '"+faq.getCount()+"', '', '"+faq.getAnswer()+"' )");
        try {
            faqDao.getJdbcTemplate().execute(sql.toString());
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    /**
     * 编辑条目
     */
    public void updateFaq(Faq faq) {

        StringBuilder sql = new StringBuilder();
        sql.append("UPDATE faq SET ");
        sql.append("question='" + faq.getQuestion()+"', ");
        sql.append("remarks='" + faq.getRemarks()+"', ");
        sql.append("count='" + faq.getCount()+"', ");
        sql.append("url='" + faq.getUrl()+"', ");
        sql.append("answer='" + faq.getAnswer()+"'");
        sql.append(" WHERE id ='" + faq.getId()+"'");

        try {
            faqDao.getJdbcTemplate().execute(sql.toString());
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    /**
     * 删除条目
     */
    public void deleteFaq(Faq faq) {
//        DELETE FROM 表名称 WHERE 列名称 = 值
        StringBuilder sql = new StringBuilder();
        sql.append("DELETE FROM faq  ");
        sql.append(" WHERE id ='" + faq.getId()+"'");

        try {
            faqDao.getJdbcTemplate().execute(sql.toString());
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    public Map<String, Object> getLayuiList(int page, int limit) {
        Map<String, Object> map = new HashMap<>();

        //数据状态的字段名称，默认：code 0
        //状态信息的字段名称，默认：msg
        //数据总数的字段名称，默认：count
        //数据列表的字段名称，默认：data

        try {
            //faq总条数
            int a = faqDao.getJdbcTemplate().queryForObject("SELECT COUNT(1) FROM faq", Integer.class);
            StringBuilder sql = new StringBuilder();
            sql.append("SELECT * FROM faq ");
            sql.append("LIMIT " + ((page - 1) * (limit)) + "," + limit);
            map.put("data", this.getDao().getJdbcTemplate().queryForList(sql.toString()));
            map.put("count", a);
            map.put("code", 0);
            map.put("msg", "请求成功");

            return map;
        } catch (DataAccessException e) {
            e.printStackTrace();
            map.put("code", 1);
            map.put("msg", "请求失败");

            return map;
        }

    }

}
